package cj.web.cc.dao;

import java.util.List;
import java.util.Map;

import net.paoding.rose.jade.annotation.DAO;
import net.paoding.rose.jade.annotation.ReturnGeneratedKeys;
import net.paoding.rose.jade.annotation.SQL;

@DAO
public interface ApplyDAO {
	@SQL("select * from Permit where deleteFlag=1 and gameId=:1 and code=:2 ")
	List<Map> validate(int gameId, String code);

	@SQL("select id as permitId,gameId,areaName,schoolName,address,code,returnCode from Permit where id=:1 ")
	List<Map> getPermitById(int permitId);

	@SQL("select name,duration,type,price,img,maxApplyCount,startDate,endDate from Game where id=:1 ")
	List<Map> getGameById(int gameId);
	
	@SQL("select id,name from Grade where deleteFlag=1 ")
    List<Map> asGradeEnum();

	@ReturnGeneratedKeys
	@SQL("INSERT INTO GameOrder (orderNo,userId,gameId,duration,permitId,areaName,schoolName,address,code,returnCode,price,parentName,parentPhone,name,identityCard,school,grade,source,status,deleteFlag,createAt,createUser,updateAt,updateUser,area,teacher) VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,1,now(),:20,now(),:20,:21,:22) ")
	int createOrder(String orderNo, String memberId, int gameId,
			String duration, int permitId, String areaName, String schoolName,
			String address, String code, String returnCode, String price,
			String parentName, String parentPhone, String name,
			String identityCard, String school, String grade,
			int source, int status, String phone, String area, String teacher);

	@SQL("update Permit set status=:2,updateUser=:3,updateAt=NOW() where id=:1 ")
	void changeStatusById(int permitId, int status, String phone);

	@SQL("SELECT id,name FROM Area WHERE deleteFlag=1 ")
	List<Map> asAreaEnum();

	@SQL("select id,permitId,price,status,payOrder,code,returnCode,duration,schoolName,name,userId from GameOrder where deleteFlag=1 and orderNo=:1 ")
	List<Map> getOrderByNo(String outTradeNo);

	@SQL("update GameOrder set payOrder=:2,status=:3,payWay=:4,updateAt=NOW() where orderNo=:1 ")
	void updatePayStatusByNo(String out_trade_no, String payOrder, int status, int payWay);
	
	@SQL("update GameOrder set status=:2,updateAt=NOW() where id=:1 ")
	void updateOrderStatusById(int id, int status);

	@SQL("SELECT id,orderNo,userId,gameId,duration,permitId,areaName,schoolName,address,code,returnCode,price,parentName,parentPhone,name,identityCard,school,grade,payWay,source,status,deleteFlag,createAt,createUser,updateAt,updateUser,area,teacher FROM GameOrder WHERE deleteFlag=1 AND id=:1 ")
	List<Map> getOrderById(int orderId);
	
	@SQL("SELECT count(1) FROM GameOrder WHERE deleteFlag=1 AND identityCard=:1 AND gameId=:2 AND id not in(:3)")
	int checkOrderByIdentityCard(String identityCard,int gameId,int orderId);

	@SQL("select id as permitId,gameId,areaName,schoolName,address,code,returnCode,pwd from Permit where deleteFlag=1 and id=:1 and status=1 ")
	List<Map> getValidatePermitById(int permitId);
	
	@SQL("UPDATE GameOrder SET name=:2,identityCard=:3,area=:4,school=:5,grade=:6,teacher=:7,parentName=:8,parentPhone=:9,updateAt=now(),updateUser=:10 WHERE id=:1 ")
	void updateOrder(int orderId,String name,String identityCard,String area,String school,String grade,String teacher,
			String parentName,String parentPhone,String updateUser);
	
	@SQL("UPDATE GameOrder SET identityCard=:2,area=:3,school=:4,grade=:5,teacher=:6,parentName=:7,parentPhone=:8,updateAt=now(),updateUser=:9 WHERE id=:1 ")
	void updateOrderForYFK(int orderId,String identityCard,String area,String school,String grade,String teacher,
			String parentName,String parentPhone,String updateUser);

	@SQL("SELECT id,orderNo,userId,gameId,duration,permitId,areaName,schoolName,address,code,returnCode,price,parentName,parentPhone,name,identityCard,school,grade,payWay,source,status,deleteFlag,createAt,createUser,updateAt,updateUser,area,teacher FROM GameOrder WHERE deleteFlag=1 AND userId=:1 ORDER BY updateAt DESC ")
	List<Map> getOrderByUserId(int userId);

	@SQL("select createUser,status from GameOrder where deleteFlag=1 and status!=2 and identityCard=:1 and gameId=:2 ")
	List<Map> getOrderByIDCard(String identityCard, int gameId);

	@SQL("SELECT id,orderNo,userId,gameId,duration,permitId,areaName,schoolName,address,code,returnCode,price,parentName,parentPhone,name,identityCard,school,grade,payWay,source,status,deleteFlag,createAt,createUser,updateAt,updateUser,area,teacher FROM GameOrder WHERE deleteFlag=1 AND code=:1 AND gameId=:2 ")
	List<Map> getOrderByCode(String code,int gameId);
	
	@SQL("SELECT id,orderNo,userId,gameId,duration,permitId,areaName,schoolName,address,code,returnCode,price,parentName,parentPhone,name,identityCard,school,grade,payWay,source,status,deleteFlag,createAt,createUser,updateAt,updateUser,area,teacher FROM GameOrder WHERE deleteFlag=1 AND userId=:1 AND gameId=:2 ")
	List<Map> getOrderByUserId(int userId,int gameId);
	
	@SQL("SELECT id,orderNo,userId,gameId,duration,permitId,areaName,schoolName,address,code,returnCode,price,parentName,parentPhone,name,identityCard,school,grade,payWay,source,status,deleteFlag,createAt,createUser,updateAt,updateUser,area,teacher FROM GameOrder WHERE deleteFlag=1 AND userId=:1 AND gameId=:2 AND code=:3 ")
	List<Map> getOrderByUserId(int userId,int gameId,String code);
	
	@SQL("SELECT id,orderNo,userId,gameId,duration,permitId,areaName,schoolName,address,code,returnCode,price,parentName,parentPhone,name,identityCard,school,grade,payWay,source,status,deleteFlag,createAt,createUser,updateAt,updateUser,area,teacher FROM GameOrder WHERE deleteFlag=1 AND userId=:1 ORDER BY createAt DESC LIMIT :2,:3 ")
	List<Map> getOrderByUserId(int userId,int start,int length);
	
	@SQL("SELECT count(1) FROM GameOrder WHERE deleteFlag=1 AND userId=:1 ORDER BY createAt DESC ")
	int getOrderByUserIdSize(int userId);
	
	@SQL("SELECT gameId,code,level,name,teacher,school,grade,parentName,parentPhone,payStatus,path FROM ReportCard WHERE gameId=:1 AND code =:2 ")
    List<Map> queryByGameIdAndCode(int gameId,String code);
	
	@SQL("SELECT count(1) FROM ReportCard WHERE gameId=:1 ")
    int queryByGameIdCount(int gameId);
	
	//查询
	@SQL("SELECT r.id,r.gameId,code,level,r.name,teacher,school,grade,parentName,parentPhone,payStatus,path,g.name as gamename FROM ReportCard as r,game as g WHERE  r.gameId = g.id and r.gameId=:1 #if(:2!=''){ AND code LIKE '%##(:2)%' } #if(:3!=''){ AND r.name LIKE '%##(:3)%' } LIMIT :4,:5 ")
    List<Map> queryReportCardByGCN(int gameId, String code, String name, int start, int length);
	
	//获取活动名称
	@SQL("SELECT name FROM Game WHERE deleteFlag=1 AND Id=:1")
	List<Map> getGameName(int gameId);
	
	//总数
	@SQL("SELECT count(1) FROM ReportCard WHERE gameId=:1 #if(:2!=''){ AND code LIKE '%##(:2)%' } #if(:3!=''){ AND name LIKE '%##(:3)%' } ")
    int queryReportCardByGCNSzie(int gameId, String code, String name);
	
	//学校模糊匹配
	@SQL("SELECT name as value FROM TBSchool WHERE deleteFlag=1 AND areaName LIKE '%##(:1)%' ")
	List<Map> queryTBSchoolName(String areaName);
	
}